﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Linq;
using System.Text;
using CommunitySystemModel;
using System.Data.SqlClient;

namespace CommunitySystem.BO
{
    public class JinShengBO
    {

        public bool SaveJinShen_MainInfor(JinShen_MainInfo entity)
        {
            try
            {
                if (entity.ID > 0)
                {
                    string sql = "delete JinShen_DetailInfo where MainID=" + entity.ID;
                    AccessHelper.GetInstance().ExecuteNonQuery(sql);
                    if (entity.MId <= 0)
                    {
                        sql = "delete JinShen_MainInfo_Attatchment where MainID=" + entity.ID;
                        AccessHelper.GetInstance().ExecuteNonQuery(sql);
                    }
                    //else
                    //{
                    //    sql =string.Format( "update JinShenM set status='{0}' where ID={1}" ,entity.Status, entity.MId);
                    //    AccessHelper.GetInstance().ExecuteNonQuery(sql);
                    //}

                }
                int id = AccessHelper.GetInstance().SaveEntity(entity, "", new List<string>() { "Details", "Attachment", "JinShenM", "ChooseCandidator_Main", "KaoCha_Detail", "DangGongWei_Detail", "PublicShow_Detail", "OKRenM_Detail" });

                if (entity.Details != null)
                {
                    foreach (var detail in entity.Details)
                    {
                        detail.ID = 0;
                        detail.MainID = id;
                        AccessHelper.GetInstance().SaveEntity(detail, "", null);
                    }
                }
                if (entity.Attachment != null)
                {
                    foreach (var attachment in entity.Attachment)
                    {
                        attachment.ID = 0;
                        attachment.MainID = id;
                        AccessHelper.GetInstance().SaveEntity(attachment, "", null);
                    }
                }
            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }

        public int SaveJinShen_MainInfors(ObservableCollection<JinShen_MainInfo> entitys, int edit = 0, JinShen_MainInfo_Attatchment att = null)
        {
            int result = 0;
            try
            {
                int Mid = -1;
                if (entitys.Count > 0)
                {
                    if (edit > 0)
                    {
                        //if (att != null)
                        //{
                        JinShenM M = entitys.First().JinShenM;
                        M.ID = entitys.First().MId;
                        M.OrgID = entitys.First().OrgID;
                        M.OrgName = entitys.First().OrgName;
                        string sql = string.Format(@"UPDATE JinShenM
                                                   SET orgid={0} ,orgname='{1}' WHERE id={2} ;
                                                    UPDATE JinShen_MainInfo
                                                   SET orgid={0} ,orgname='{1}' WHERE MId={2}", M.OrgID, M.OrgName, M.ID);
                        AccessHelper.GetInstance().ExecuteNonQuery(sql);



                        if (att != null)
                        {
                            string sql11 = string.Format(@"if exists(select * from JinShen_MainInfo_Attatchment where [MainID]={6})
                                    UPDATE JinShen_MainInfo_Attatchment
                                                   SET  IsBTg=1  WHERE [MainID]={6}  
                            insert into JinShen_MainInfo_Attatchment ([Key],[filename],[realname],[Status],[FileSize],[Percentage],[MainID])
                                                 values('{0}','{1}','{2}','{3}','{4}','{5}','{6}')
                                
                            ", att.Key, att.filename, att.realname, att.Status, att.FileSize, att.Percentage, att.MainID);
                            AccessHelper.GetInstance().ExecuteNonQuery(sql11);
                        }
                        //}
                        //else  //审核
                        //{
                        var Nojsm = entitys.Where(p => p.Status == "审核不通过").ToList();
                        var isTjjsm = entitys.Where(p => p.Status == "审核通过").ToList();
                        var jsry = entitys.Where(p => p.Status == "人员审核").ToList();
                        var Nojsrysh = entitys.Where(p => p.Status == "人员审核不通过").ToList();
                        var jsrysh = entitys.Where(p => p.Status == "人员审核通过").ToList();
                        var rehd = entitys.Where(p => p.Status == "人员核定").ToList();
                        var rekc = entitys.Where(p => p.Status == "人员考察").ToList();

                        var bmck = entitys.Where(p => p.Status == "主管部门查看").ToList();
                        if (bmck.Count == entitys.Count)
                        {
                            string sql1 = string.Format(@"UPDATE JinShenM SET [Status]='主管部门查看',dsc='' WHERE id={0} "
                                , bmck.First().MId);
                            AccessHelper.GetInstance().ExecuteNonQuery(sql1);
                        }
                        if (bmck.Count < entitys.Count && bmck.Count != 0)
                        {
                            string sql1 = string.Format(@"UPDATE JinShenM SET [Status]='人员审核通过',dsc='' WHERE id={0} "
                                , bmck.First().MId);
                            AccessHelper.GetInstance().ExecuteNonQuery(sql1);
                        }

                        var bmckt = entitys.Where(p => p.Status == "已查看").ToList();
                        if (bmckt.Count == entitys.Count)
                        {
                            string sql1 = string.Format(@"UPDATE JinShenM SET [Status]='已查看',dsc='' WHERE id={0} "
                                , bmckt.First().MId);
                            AccessHelper.GetInstance().ExecuteNonQuery(sql1);
                        }
                        if (bmckt.Count < entitys.Count && bmckt.Count != 0)
                        {
                            string sql1 = string.Format(@"UPDATE JinShenM SET [Status]='主管部门查看',dsc='' WHERE id={0} "
                              , bmckt.First().MId);
                            AccessHelper.GetInstance().ExecuteNonQuery(sql1);
                        }

                        if (Nojsm.Count > 0)
                        {
                            string dsc = "";
                            foreach (var item in entitys)
                            {
                                dsc += item.DSC + ";";
                            }
                            string sql1 = string.Format(@"if exists(select * from JinShenM where isbtg=0 and ID={0})
                                        UPDATE JinShenM  SET [Status]='审核不通过',isbtg=1,dsc='{1}'  WHERE id={0}
                                             else   UPDATE JinShenM  SET [Status]='再次申请',isbtg=0,dsc=''
                                                 WHERE id={0}", Nojsm.First().MId, dsc);
                            AccessHelper.GetInstance().ExecuteNonQuery(sql1);
                        }
                        if (isTjjsm.Count == entitys.Count)
                        {

                            string sql1 = string.Format(@"if exists(select * from JinShenM where isbtg=1 and ID={0})
                                                    UPDATE JinShenM SET [Status]='再次申请',dsc='' WHERE id={0}
                                                    else
                                                    UPDATE JinShenM SET [Status]='审核通过',dsc='' WHERE id={0}"
                                , isTjjsm.First().MId);
                            AccessHelper.GetInstance().ExecuteNonQuery(sql1);
                        }
                        if (jsry.Count == entitys.Count || (entitys.Count != 1 && jsry.Count !=0&&(jsry.Count + jsrysh.Count) == entitys.Count))
                        {

                            string sql1 = string.Format(@"if exists(select * from JinShenM where isbtg=0 and ID={0})
                                        UPDATE JinShenM  SET [Status]='人员审核',isbtg=1,dsc=''  WHERE id={0}
                                             else   UPDATE JinShenM  SET [Status]='再次人员审核',isbtg=0,dsc=''
                                                 WHERE id={0}", jsry.First().MId);


                            AccessHelper.GetInstance().ExecuteNonQuery(sql1);
                        }

                        if (Nojsrysh.Count > 0)
                        {
                            string dsc = "";
                            foreach (var item in entitys)
                            {
                                dsc += item.DSC + ";";
                            }
                            string sql1 = string.Format(@"
                                        UPDATE JinShenM  SET [Status]='人员审核不通过',isbtg=1,dsc='{1}'  WHERE id={0}
                                         ", Nojsrysh.First().MId, dsc);


                            AccessHelper.GetInstance().ExecuteNonQuery(sql1);
                        }
                        if (jsrysh.Count == entitys.Count)
                        {
                            string sql1 = string.Format(@"
                                                    UPDATE JinShenM SET [Status]='人员审核通过',dsc='' WHERE id={0}"
                                , jsrysh.First().MId);
                            AccessHelper.GetInstance().ExecuteNonQuery(sql1);
                        }
                        if (rehd.Count == entitys.Count)
                        {
                            string sql1 = string.Format(@"UPDATE JinShenM
                                                   SET [Status]='人员核定'
                                                 WHERE id={0}", rehd.First().MId);
                            AccessHelper.GetInstance().ExecuteNonQuery(sql1);
                        }
                        if (rekc.Count == entitys.Count)
                        {
                            string sql1 = string.Format(@"UPDATE JinShenM
                                                   SET [Status]='人员考察'
                                                 WHERE id={0}", rekc.First().MId);
                            AccessHelper.GetInstance().ExecuteNonQuery(sql1);
                        }
                        //}

                    }
                    else
                    {
                        if (entitys.Last().ID <= 0)
                        {
                            JinShenM M = entitys.First().JinShenM;
                            M.OrgID = entitys.First().OrgID;
                            M.OrgName = entitys.First().OrgName;
                            Mid = AccessHelper.GetInstance().SaveEntity(entitys.First().JinShenM, "", null);

                            foreach (var item in entitys)
                            {
                                JinShen_MainInfo entity = item;
                                entity.MId = Mid;
                                int id = AccessHelper.GetInstance().SaveEntity(entity, "", new List<string>() { "Details", "Attachment", "JinShenM", "ChooseCandidator_Main", "KaoCha_Detail", "DangGongWei_Detail", "PublicShow_Detail", "OKRenM_Detail" });
                            
                             
                            }
                        }
                        //修改一个岗位
                        if (entitys.First().ID > 0)
                        {


                            foreach (var item in entitys)
                            {

                                JinShen_MainInfo entity = item;
                                entity.MId = entitys.First().MId;
                                int id = AccessHelper.GetInstance().SaveEntity(entity, "", new List<string>() { "Details", "Attachment", "JinShenM", "ChooseCandidator_Main", "KaoCha_Detail", "DangGongWei_Detail", "PublicShow_Detail", "OKRenM_Detail" });
                            }


                        }
                        //方案
                        if (att != null)
                        {
                            att.MainID = Mid;
                            AccessHelper.GetInstance().SaveEntity(att, "", null);
                            result = Mid;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                return -1;
            }

            return result;
        }

        #region   Emily
        public bool UploadFile1(int Mid, string key, string filename, byte[] content, string realname)
        {
            bool flag = false;
            string sql = string.Format(@"update JinShen_MainInfo_Attatchment set TempUrl=@TempUrl
                                    where mainId={0}", Mid);
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@TempUrl", SqlDbType.Image);
            parameter[0].Value = content;
            if (new AccessHelper().ExecuteSql(sql, parameter) > 0)
            {
                flag = true;
            }

            return flag;
        }
        public byte[] ReadWord(int id)
        {
            string sql = string.Format("select * from JinShen_MainInfo_Attatchment  where MainID={0}", id);

            DataSet db = new AccessHelper().ExcuteDataSet(sql);
            byte[] WordByte = (byte[])db.Tables[0].Rows[0]["TempUrl"];
            return WordByte;
        }
        #endregion
        public ObservableCollection<JinShen_MainInfo> GetJinshenMainInfo(string condition, string LoginUser)
        {
            string sql = string.Format(@"select r.RoleName,u.OrgCode from UserInfo u
left join Roles r on r.id = u.roleID
where u.loginname='{0}'", LoginUser);
            List<string> SuperAdmin = new List<string>()
            {
                "管理员",
                "系统操作"
            };

            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            string role = ds.Tables[0].Rows[0][0] as string;
            string code = ds.Tables[0].Rows[0][1] as string;
            if (!SuperAdmin.Contains(role))
            {
                if (string.IsNullOrEmpty(condition))
                {
                    //  condition = string.Format(" and CreateUser = '{0}'", LoginUser);
                    condition = string.Format(" and Orgid in ({0}) ", code);
                }
                else
                {
                    //  condition += string.Format("and CreateUser = '{0}'", LoginUser);
                    condition += string.Format(" and Orgid in ({0}) ", code);
                }
            }
            var mains = AccessHelper.GetInstance().GetEnity<JinShen_MainInfo>(condition, "");
            foreach (var main in mains)
            {
                main.Details = AccessHelper.GetInstance().GetEnity<JinShen_DetailInfo>("MainID=" + main.ID, "");
                main.Attachment = AccessHelper.GetInstance().GetEnity<JinShen_MainInfo_Attatchment>("MainID=" + main.ID, "");
            }

            return mains;
        }
        public ObservableCollection<JinShenMHelp> GetJinshenMainInfos(string condition, string LoginUser)
        {
            string sql = string.Format(@"select r.RoleName,u.OrgCode from UserInfo u
left join Roles r on r.id = u.roleID
where u.loginname='{0}'", LoginUser);
            List<string> SuperAdmin = new List<string>()
            {
                "管理员",
                "系统操作"
            };

            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            string role = ds.Tables[0].Rows[0][0] as string;
            string code = ds.Tables[0].Rows[0][1] as string;
            if (!SuperAdmin.Contains(role))
            {
                if (string.IsNullOrEmpty(condition))
                {
                   // condition = string.Format("CreateUser = '{0}'  order by createdate desc", LoginUser);
                    condition = string.Format(" jsm.orgid in ({0})  order by createdate desc", code);
                }
                else
                {
                   // condition += string.Format("and CreateUser = '{0}'  order by createdate desc", LoginUser);
                    condition += string.Format(" and jsm.orgid in ({0})  order by createdate desc", code);
                }
            }
            else
                condition += string.Format(" order by createdate desc");


            string sqls = @"select jsm.*
,Status1=(case when jsm.Status='已查看' then '竞争上岗及民主测评情况已查看' else jsm.Status end)
,jma.MainID,jma.[Key],jma.[filename],jma.realname ,jma.Percentage,jma.FileSize 
                            ,OpStatus=case when jsm.status='提交申请' then '晋升方案审核' 
                            when jsm.status='审核通过' then '增加人员'
                            when jsm.status='审核不通过' then ''
                            when jsm.status='再次申请' then '晋升方案审核'
                            when jsm.status='人员审核' then '人员审核'
                            when jsm.status='人员审核通过' then '民主测评及竞争上岗'

                            when jsm.status='主管部门查看' then '民主测评及竞争上岗'
                            when jsm.status='已查看' then '人员核定'

                            when jsm.status='审核通过' then '增加人员'
                            when jsm.status='人员审核不通过' then '增加人员'
                            when jsm.status='再次人员审核' then '人员审核'
                            when jsm.status='人员核定' then '人员核定'
                            when jsm.status='人员考察' then '晋升详情'
                            when jsm.status='人员考察' then '上党工委组会议'
                            when jsm.status='上党工委组会议' then '候选人公示'
                            when jsm.status='候选人公示' then '晋升确认'
                            when jsm.status='晋升确认' then '晋升办理'
                              when jsm.status='退回' then '晋升确认'
                            when jsm.status='晋升成功' then '已办理'
                            end
                            from dbo.JinShenM jsm left join JinShen_MainInfo_Attatchment jma
                            on jsm.id=jma.MainID and jma.IsBTg=0";
            var mains = AccessHelper.GetInstance().GetEnityJoin<JinShenMHelp>(sqls, condition, "");


            foreach (var main in mains)
            {
                main.JinShen_MainInfos = AccessHelper.GetInstance().GetEnity<JinShen_MainInfo>("MID=" + main.ID, "");
                foreach (var item in main.JinShen_MainInfos)
                {
                    item.Details = AccessHelper.GetInstance().GetEnity<JinShen_DetailInfo>("MainID=" + item.ID, "");

                    item.ChooseCandidator_Main = AccessHelper.GetInstance().GetEnity<JinShen_ChooseCandidator_Main>("MainID=" + item.ID, "");
                    foreach (var main1 in item.ChooseCandidator_Main)
                    {
                        main1.Details = AccessHelper.GetInstance().GetEnity<JinShen_ChooseCandidator_Detail>("ChooseMainID=" + main1.ID, "");
                    }
                    item.KaoCha_Detail = AccessHelper.GetInstance().GetEnity<JinShen_KaoCha_Detail>("MainID=" + item.ID, "");

                    item.DangGongWei_Detail = AccessHelper.GetInstance().GetEnity<JinShen_DangGongWei_Detail>("MainID=" + item.ID, "");

                    item.PublicShow_Detail = AccessHelper.GetInstance().GetEnity<JinShen_PublicShow_Detail>("MainID=" + item.ID, "");

                    item.OKRenM_Detail = AccessHelper.GetInstance().GetEnity<JinShen_OKRenM_Detail>("MainID=" + item.ID, "");
                }
            }


            return mains;
        }



        public ObservableCollection<Emp_BasicInfoModel> GetEmpBasicInfo(string Condition)
        {
            return AccessHelper.GetInstance().GetEnity<Emp_BasicInfoModel>(Condition, "Emp_BasicInfo");
        }

        public ObservableCollection<Emp_BasicInfoModel> GetEmpBasicInfo_zj(string Condition,int orgid)
        {
            string sb = string.Format(@"SELECT emp.Name, emp.ID, emp.CardNo,emp.OrgID FROM dbo.Emp_BasicInfo emp");
            ObservableCollection<Emp_BasicInfoModel> list = new ObservableCollection<Emp_BasicInfoModel>();
            if (!string.IsNullOrEmpty(Condition))
            {
                sb += string.Format(" where emp.OrgID={1} {0}", Condition, orgid);
            }
            //整建
            sb += @" union
                select  emp.Name, emp.ID, emp.CardNo,emp.OrgID  from ZJZZR_Emp zj
                left join Emp_BasicInfo emp on zj.EId=emp.ID
                 where  bz='未转任'";
            if (!string.IsNullOrEmpty(Condition))
            {
                sb += string.Format("  and zj.ZRDWID={1} {0}", Condition, orgid);
            }

            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sb.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    Emp_BasicInfoModel emp = new Emp_BasicInfoModel();
                    emp.ID = Convert.ToInt32(dr["ID"]);
                    emp.Name = Convert.ToString(dr["Name"]);
                    emp.CardNo = Convert.ToString(dr["CardNo"]);
                    emp.OrgID = Convert.ToInt32(dr["OrgID"]);
                    list.Add(emp);
                }
            }
            return list;
        }

        public ObservableCollection<Emp_BasicInfoModel> GetEmpBasicInfoAccordingToDuty(string condition)
        {
            StringBuilder sb = new StringBuilder(@"SELECT emp.Name, emp.ID, emp.CardNo,emp.OrgID FROM dbo.Emp_BasicInfo emp
INNER JOIN dbo.Emp_Duty duty ON emp.ID = duty.EmpbasicID  AND duty.JobStatus = '在任'");
            ObservableCollection<Emp_BasicInfoModel> list = new ObservableCollection<Emp_BasicInfoModel>();
            if (!string.IsNullOrEmpty(condition))
            {
                sb.Append("    where  ");
                sb.Append(condition);
            }
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sb.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    Emp_BasicInfoModel emp = new Emp_BasicInfoModel();
                    emp.ID = Convert.ToInt32(dr["ID"]);
                    emp.Name = Convert.ToString(dr["Name"]);
                    emp.CardNo = Convert.ToString(dr["CardNo"]);
                    emp.OrgID = Convert.ToInt32(dr["OrgID"]);
                    list.Add(emp);
                }
            }
            return list;
        }


        public ObservableCollection<Emp_BasicInfoModel> GetEmpBasicInfoAccordingToMianShi(int year,int id,string name)
        {
            string sb = string.Format(@"SELECT XingMing Name,SFZH CardNo,z.ID,zdk.id ZhaoLu_DaoRuKaoShengID FROM dbo.ZhaoLu_DaoRuKaoSheng zdk
inner join ZhaoLuZhiWei z on zdk.ZWID = z.ZhiWeiDaiMa
where z.Year = {0} and z.ID = {1} ",year,id);
            if (!string.IsNullOrEmpty(name))
                sb += string.Format(" and XingMing like '%{0}%' or SFZH like '%{0}%'", name);
            ObservableCollection<Emp_BasicInfoModel> list = new ObservableCollection<Emp_BasicInfoModel>();
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sb.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    Emp_BasicInfoModel emp = new Emp_BasicInfoModel();
                    emp.ID = Convert.ToInt32(dr["ID"]);
                    emp.Name = Convert.ToString(dr["Name"]);
                    emp.CardNo = Convert.ToString(dr["CardNo"]);
                    emp.OrgID = Convert.ToInt32(dr["ZhaoLu_DaoRuKaoShengID"]);
                    list.Add(emp);
                }
            }
            return list;
        }


        public ObservableCollection<Emp_BasicInfoModel> GetEmpBasicInfoAccordingToDuty_ZJ(string condition, int orgid)
        {
//            StringBuilder sb = new StringBuilder(@"SELECT emp.Name, emp.ID, emp.CardNo,emp.OrgID FROM dbo.Emp_BasicInfo emp
//INNER JOIN dbo.Emp_Duty duty ON emp.ID = duty.EmpbasicID  AND duty.JobStatus = '在任'");
            string sb = string.Format(@"SELECT emp.Name, emp.ID, emp.CardNo,emp.OrgID FROM dbo.Emp_BasicInfo emp
INNER JOIN dbo.Emp_Duty duty ON emp.ID = duty.EmpbasicID  AND duty.JobStatus = '在任'");
            ObservableCollection<Emp_BasicInfoModel> list = new ObservableCollection<Emp_BasicInfoModel>();
            if (!string.IsNullOrEmpty(condition))
            {
                sb += string.Format(" where emp.OrgID={1} {0}", condition,orgid);
            }
            //整建
            sb += @" union
                select  emp.Name, emp.ID, emp.CardNo,emp.OrgID  from ZJZZR_Emp zj
                left join Emp_BasicInfo emp on zj.EId=emp.ID
                left JOIN dbo.Emp_Duty duty ON zj.EId = duty.EmpbasicID  AND duty.JobStatus = '在任'   
                 where  bz='未转任'";
 if (!string.IsNullOrEmpty(condition))
            {
                sb += string.Format("  and zj.ZRDWID={1} {0}", condition, orgid);
            }

            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sb.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    Emp_BasicInfoModel emp = new Emp_BasicInfoModel();
                    emp.ID = Convert.ToInt32(dr["ID"]);
                    emp.Name = Convert.ToString(dr["Name"]);
                    emp.CardNo = Convert.ToString(dr["CardNo"]);
                    emp.OrgID = Convert.ToInt32(dr["OrgID"]);
                    list.Add(emp);
                }
            }
            return list;
        }


        public string GetOrgForHR(string loginUser)
        {
            string sql = string.Format(@"select r.RoleName from UserInfo u 
left join Roles r on r.id = u.roleID
where u.loginname='{0}'", loginUser);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            return ds.Tables[0].Rows[0][0] as string;
        }

        public bool SaveJinShen_ChooseCandidator_Main(JinShen_ChooseCandidator_Main entity)
        {
            try
            {
                if (entity.ID > 0)
                {
                    string sqlDeleteDetail = "delete JinShen_ChooseCandidator_Detail where ChooseMainID=" + entity.ID;
                    AccessHelper.GetInstance().ExecuteNonQuery(sqlDeleteDetail);
                    AccessHelper.GetInstance().SaveEntity(entity, "", new List<string>() { "Details" });
                    foreach (var d in entity.Details)
                    {
                        d.ID = 0;
                        d.ChooseMainID = entity.ID;
                        AccessHelper.GetInstance().SaveEntity(d, "", null);
                    }
                }
                else
                {
                    int id = AccessHelper.GetInstance().SaveEntity(entity, "", new List<string>() { "Details" });
                    foreach (var d in entity.Details)
                    {
                        d.ChooseMainID = id;
                        AccessHelper.GetInstance().SaveEntity(d, "", null);
                    }
                }
            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }

        public string DownLoadFile(string key, string GuidName, string RealName)
        {
            string filePath = System.Web.Configuration.WebConfigurationManager.AppSettings[key].ToString() + @"\" + GuidName;
            string saveFolder = System.Web.Hosting.HostingEnvironment.MapPath("~/cash" + @"\" + RealName);
            if (System.IO.File.Exists(filePath))
            {
                try
                {
                    System.IO.File.Copy(filePath, saveFolder, true);
                }
                catch (Exception ex)
                {
                    return "";
                }
                return "/cash/" + RealName;
            }


            return "";
        }

        public bool ComfirmJinShen(int MainInfoID, string Stauts)
        {
            AccessHelper acc = new AccessHelper();

            if ((Stauts == "晋升确认" || Stauts == "晋升成功") && MainInfoID > 0)//确认晋升  完成一系列动作
            {
                try
                {


                    // 简历信息加一条
                    // 职务加一条  在任  前面的改为不在任
                    // Chegai 信息增加一条 
                    StringBuilder sql = new StringBuilder(string.Format("UPDATE dbo.JinShen_MainInfo SET Status = '{0}' WHERE ID ={1} ;UPDATE dbo.JinShenM SET Status = '{0}' WHERE ID =(select Mid from JinShen_MainInfo where id={1})", Stauts, MainInfoID));
                    acc.ExcultData(sql.ToString());
                    #region 新增简历信息

                    sql.Clear();
                    //修改上一条简历的结束日期
                    sql.Append(string.Format(@"declare @empid int,@resId int 
                                SELECT @empid=EmpID FROM JinShen_OKRenM_Detail WHERE MainID={0}
                                select top 1 @resId=ID from Emp_Resume where EmpbasicID=@empid ORDER BY BeginDate DESC
                                update Emp_Resume set EndDate=ok.StartDate
                                from Emp_Resume e,JinShen_OKRenM_Detail ok
                                where e.EmpbasicID=ok.EmpID and ok.MainID={0} and e.ID=@resId ", MainInfoID));
                    //插入新的简历信息
                    sql.Append(string.Format(@" INSERT INTO dbo.Emp_Resume 
                                      ( EmpbasicID ,BeginDate , Unit ,Duty ,IsPeriod)
                                      SELECT  emp.ID , ok.StartDate ,main.OrgName ,(main.Department+main.Position),0 
	                                  FROM dbo.Emp_BasicInfo emp
	                                  LEFT JOIN dbo.JinShen_OKRenM_Detail ok ON ok.EmpID = emp.ID
	                                  LEFT JOIN dbo.JinShen_MainInfo main ON main.ID = ok.MainID
	                                  WHERE main.ID = {0}", MainInfoID));
                    acc.ExcultData(sql.ToString());

                    #endregion
                    #region Chegai 信息增加一条
                    sql.Clear();
                    sql.Append(string.Format(@"        
       DECLARE @EmpID INT , @NewPositionLevel  NVARCHAR(20), @OldPositionLevel NVARCHAR(20)
        SELECT  @EmpID = detail.EmpID FROM dbo.JinShen_MainInfo main 
        LEFT JOIN dbo.JinShen_PublicShow_Detail detail ON detail.MainID = main.ID 
        WHERE main.ID = {0}

SELECT @NewPositionLevel=AttributLevel FROM dbo.JinShen_DangGongWei_Detail where EmpID=@EmpID and MainID={0} and IsAgree='同意'


        SELECT @OldPositionLevel = DutyLevel FROM dbo.Emp_Duty WHERE JobStatus='在任' AND EmpbasicID= @EmpID
        IF(@NewPositionLevel != @OldPositionLevel)
        BEGIN
			 INSERT INTO CheGai
           ([EmpID] ,[OldZhiJi],[NewZhiJi] ,[Status] ,[Demo] ,[RelationID],KeyID,[type],CreateDate)
     select  detail.EmpID ,@OldPositionLevel,pd.AttributLevel, '未导出' ,'晋升工作',main.ID,
     case when (select count(cg.id) Cou from chegai cg inner join Emp_BasicInfo emp on cg.empid = emp.id
      where cg.status = '未导出' and type='变动表' and emp.orgid = 
      (select orgid from Emp_BasicInfo where id = detail.EmpID)) > 0 then
      (select top 1 keyid from chegai cg inner join Emp_BasicInfo emp on cg.empid = emp.id
      where cg.status = '未导出' and type='变动表' and emp.orgid = 
      (select orgid from Emp_BasicInfo where id = detail.EmpID)) else
      (select top 1 id + 1 from chegai order by id desc) end KeyID,
        '变动表',
        GETDATE()
     FROM dbo.JinShen_MainInfo main 
        LEFT JOIN dbo.JinShen_PublicShow_Detail detail ON detail.MainID = main.ID 
  LEFT JOIN dbo.JinShen_DangGongWei_Detail pd ON pd.MainID = main.ID  and pd.EmpID=detail.EmpID
        WHERE main.ID = {0} and main.orgid not in (46,47)       
        
        END", MainInfoID));
                    acc.ExcultData(sql.ToString());
                    #endregion

                    #region 职务加一条  在任  前面的改为不在任    职务层次变属性一定变=>层次属性时间都变确认开始时间  层次不变属性变=>层次上条的职务任现职务层次时间，属性变确认开始时间
                    sql.Clear();
                    sql.Append(string.Format(@"DECLARE @empid INT,@yzwcc varchar(200),@yzwsx varchar(200),
                    @zwcc varchar(200),@zwsx varchar(200),
                    @zfcctt varchar(200),@zfsxtt varchar(200)=''
                    SELECT @empid = EmpID FROM dbo.JinShen_OKRenM_Detail WHERE MainID={0}
                    UPDATE dbo.Emp_Duty SET JobStatus='不在任' WHERE EmpbasicID= @empid
                    select top 1 @yzwcc=DutyLevel,@yzwsx=DutyAttribute from Emp_Duty where EmpbasicID=@empid ORDER BY OKDate DESC
                    select @zwcc=AttributLevel,@zwsx=PositionAttibute from JinShen_DangGongWei_Detail where MainID={0} and EmpID=@empid
                    if(@yzwcc!=@zwcc)
                    begin
                    SELECT @zfcctt= StartDate FROM dbo.JinShen_OKRenM_Detail WHERE MainID={0}
                    set @zfsxtt=@zfcctt
                    end
                    else
                    SELECT top 1 @zfcctt= JobDate from Emp_Duty where EmpbasicID=@empid ORDER BY OKDate DESC

                    if(@yzwsx!=@zwsx and @zfsxtt='')
                    SELECT @zfsxtt= StartDate FROM dbo.JinShen_OKRenM_Detail WHERE MainID={0}
                    else
                    SELECT top 1 @zfsxtt= RXRZWSXSJ from Emp_Duty where EmpbasicID=@empid ORDER BY OKDate DESC

                    INSERT INTO dbo.Emp_Duty( EmpbasicID , DutyNo ,  DutyName , DutyLevel ,
                              DutyAttribute , OKDate ,  OrganizationName , Sec ,  WorkCharge ,
                              ServiceMode ,IsExceptionalPromotion 
                              ,JobStatus ,JobDate , RenQi ,RZSYQ 
                              ,BRZDQRQ ,LianRen , RXRZWSXSJ , GRStartDate)
                    SELECT ok.EmpID,pd.Position,Department , pd.AttributLevel 
                    , pd.PositionAttibute, ok.StartDate ,OrgName , '' ,  main.FenGuanGongZuo 
                    ,  PromotType ,  CASE detail.IsPoGe WHEN 1 THEN '是' when 0 then '否' END 
                    , '在任' , @zfcctt , ISNULL(DATEDIFF(YEAR,ok.StartDate,ok.EndDate),0) ,  ok.SYQYear 
                    ,  ok.EndDate , '' ,  @zfsxtt ,  ok.StartDate
                            FROM dbo.JinShen_MainInfo main 
                            LEFT JOIN dbo.JinShen_DetailInfo detail ON detail.MainID = main.ID AND detail.EmpBasicID =  @empid
                            LEFT JOIN dbo.JinShen_DangGongWei_Detail pd ON pd.MainID = main.ID AND pd.EmpID =  @empid        
                            left join dbo.JinShen_OKRenM_Detail ok on ok.MainID=main.ID and ok.EmpID =  @empid   
                            WHERE main.ID = {0};
                    delete from BianZhiJieYongEmp where EmpBasicID =@empid", MainInfoID));
                    acc.ExcultData(sql.ToString());
                    #endregion
                   
                    
                    #region 整建制转入  确认任命后 转任的人员相关信息改变
                    string zjsql = string.Format(@"declare @eid int,@oid int,@zje_id int,@jstype varchar(200), @zjid int,@xks varchar(200),@xzw varchar(200),@zwcc varchar(200),@zwsx varchar(200)
                select @eid=d.EmpID,@oid=jm.OrgID,@jstype=PromotType from JinShen_OKRenM_Detail d
                left join JinShen_MainInfo jm on d.MainID=jm.ID
                where MainID={0}

                select @zje_id=id,@zjid=ZJZZRId from ZJZZR_Emp where EId=@eid and ZRDWID=@oid
                select @xks=DutyName,@xzw=DutyNo,@zwcc=DutyLevel,@zwsx=DutyAttribute from Emp_Duty where EmpbasicID=@eid and JobStatus='在任'
                --确认修改相关新信息
                update ZJZZR_Emp set XKS=@xks,XZW=@xzw,ZWCC=@zwcc,ZWSX=@zwsx,BZ='已通过'+@jstype where id=@zje_id
                --确认变成新的单位
                update Emp_BasicInfo set OrgID=@oid,PositionName=@xks,NowWorkDate=(select ZRRQ from ZJZZR where ID=@zjid) where ID=@eid",MainInfoID);
                    acc.ExcultData(zjsql.ToString());
                    #endregion
                }
                catch (Exception)
                {

                    return false;
                }
        
            }
            return true;
        }

        public ObservableCollection<JinShenMHelp> GetJinshenMainInfosByCon(string condition, string LoginUser, int start, int pagesize)
        {
            ObservableCollection<JinShenMHelp> mains = new ObservableCollection<JinShenMHelp>();
            string sql = string.Format(@"select r.RoleName,u.OrgCode from UserInfo u
left join Roles r on r.id = u.roleID
where u.loginname='{0}'", LoginUser);
            List<string> SuperAdmin = new List<string>()
            {
                "管理员",
                "系统操作"
            };

            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            string role = ds.Tables[0].Rows[0][0] as string;
            string code = ds.Tables[0].Rows[0][1] as string;
            if (!SuperAdmin.Contains(role))
            {
                if (string.IsNullOrEmpty(condition))
                {
                    //  condition = string.Format(" and CreateUser = '{0}'", LoginUser);
                    condition = string.Format(" and jsm.Orgid in ({0}) ", code);
                }
                else
                {
                    //  condition += string.Format("and CreateUser = '{0}'", LoginUser);
                    condition += string.Format(" and jsm.Orgid in ({0}) ", code);
                }
            }



            string sqls = string.Format(@"select top({1}) * from
(
select ROW_NUMBER() over (order by jsm.CreateDate desc) rowID,jsm.*
,Status1=(case when jsm.Status='已查看' then '竞争上岗及民主测评情况已查看' else jsm.Status end)
,jma.MainID,jma.[Key],jma.[filename],jma.realname ,jma.Percentage,jma.FileSize 
,OpStatus=case when jsm.status='提交申请' then '晋升方案审核' 
when jsm.status='审核通过' then '增加人员'
when jsm.status='审核不通过' then ''
when jsm.status='再次申请' then '晋升方案审核'
when jsm.status='人员审核' then '人员审核'
when jsm.status='人员审核通过' then '民主测评及竞争上岗'

when jsm.status='主管部门查看' then '民主测评及竞争上岗'
when jsm.status='已查看' then '人员核定'

when jsm.status='审核通过' then '增加人员'
when jsm.status='人员审核不通过' then '增加人员'
when jsm.status='再次人员审核' then '人员审核'
when jsm.status='人员核定' then '人员核定'
when jsm.status='人员考察' then '晋升详情'
when jsm.status='人员考察' then '上党工委组会议'
when jsm.status='上党工委组会议' then '候选人公示'
when jsm.status='候选人公示' then '晋升确认'
when jsm.status='晋升确认' then '晋升办理'
when jsm.status='退回' then '晋升确认'
when jsm.status='晋升成功' then '已办理'
when jsm.status='终止办理' then '终止办理'
end
from dbo.JinShenM jsm left join JinShen_MainInfo_Attatchment jma on jsm.id=jma.MainID and jma.IsBTg=0
where 1=1 {0}
) a 
where a.rowID>=
(
SELECT ISNULL(MAX(a.rowID),0) FROM 
(
select top({1}*({2}-1)+1)* from
 (
select ROW_NUMBER() over (order by jsm.CreateDate desc) rowID,jsm.*
from dbo.JinShenM jsm left join JinShen_MainInfo_Attatchment jma on jsm.id=jma.MainID and jma.IsBTg=0
where 1=1 {0}
)bb 
)a
) order by ID desc
", condition, pagesize, start);
            //var mains = AccessHelper.GetInstance().GetEnityJoin<JinShenMHelp>(sqls, condition, "");
            ds = AccessHelper.GetInstance().ExcuteDataSet(sqls);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                JinShenMHelp khm = new JinShenMHelp();
                khm = JinShenMHelp.FillEntity(dr);
                mains.Add(khm);
            }

            foreach (var main in mains)
            {
                main.JinShen_MainInfos = AccessHelper.GetInstance().GetEnity<JinShen_MainInfo>("MID=" + main.ID, "");
                foreach (var item in main.JinShen_MainInfos)
                {
                    item.Details = AccessHelper.GetInstance().GetEnity<JinShen_DetailInfo>("MainID=" + item.ID, "");

                    item.ChooseCandidator_Main = AccessHelper.GetInstance().GetEnity<JinShen_ChooseCandidator_Main>("MainID=" + item.ID, "");
                    foreach (var main1 in item.ChooseCandidator_Main)
                    {
                        main1.Details = AccessHelper.GetInstance().GetEnity<JinShen_ChooseCandidator_Detail>("ChooseMainID=" + main1.ID, "");
                    }
                    item.KaoCha_Detail = AccessHelper.GetInstance().GetEnity<JinShen_KaoCha_Detail>("MainID=" + item.ID, "");

                    item.DangGongWei_Detail = AccessHelper.GetInstance().GetEnity<JinShen_DangGongWei_Detail>("MainID=" + item.ID, "");

                    item.PublicShow_Detail = AccessHelper.GetInstance().GetEnity<JinShen_PublicShow_Detail>("MainID=" + item.ID, "");

                    item.OKRenM_Detail = AccessHelper.GetInstance().GetEnity<JinShen_OKRenM_Detail>("MainID=" + item.ID, "");
                }
            }


            return mains;
        }

    }
}
